<?php
declare (strict_types = 1);

namespace app\command\logic;

use app\command\logic\BaseLogic;
use think\console\Output;

/**
 * 统计-商城数据
 * php think cli stats_order --msg='{"days":30}'
 */
class CliStatsOrderLogic extends BaseLogic implements CliInterface
{

    /**
     * 入口方法
     * {@inheritdoc}
     */
    public function run(Output $output, array $msg): bool
    {
        $insertData = [];
        $date       = isset($msg['date']) ? ($msg['date'] == 'today' ? date('Y-m-d') : $msg['date']) : null;
        if ( ! empty($date)) {
            $insertData[] = self::statsOrder($date, 2); // 日夕商城数据
            $insertData[] = self::statsOrder($date, 3); // 线下店铺
            $insertData[] = self::statsSupplyOrder($date, 4); // 4供应链 怡亚通
        } else {
            $days = intval($msg['days'] ?? 1);
            // 店铺ID 0 为日夕商城数据 -1 怡亚通
            // orderType 1线下直付单 2线上单 3线下单 4供应链
            for ($i = 1; $i <= $days; $i++) {
                $date = date('Y-m-d', strtotime("-$i days"));
                // echo $date . "\n";exit;
                $insertData[] = self::statsOrder($date, 2); // 日夕商城数据
                $insertData[] = self::statsOrder($date, 3); // 线下店铺
                $insertData[] = self::statsSupplyOrder($date, 4); // 4供应链 怡亚通
            }
        }
        if ( ! empty($insertData)) {
            \think\facade\Db::table('stats_order')
                ->replace()
                ->insertAll($insertData);
        }
        return true;
    }

    /**
     * 统计 日夕商城数据
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function statsOrder($date, $orderType)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods', //商品下单数/下单件数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods', // 实际支付商品数
        ];
        $data = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
        // 1线下直付单 2线上单 3线下单 4供应链
            ->whereIn('order_type', $orderType == 2 ? [2] : [1, 3])
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        $data['date']       = $date;
        $data['order_type'] = $orderType;
        // 在售产品数
        // use index idx_Status_Deleted_Type_Category_Sales
        $data['sale_goods'] = \think\facade\Db::table('goods')
            ->where('deleted', 0)
            ->where('status', 1) // 0审核中 1上架 2下架
            ->where('goods_type', $orderType == 2 ? 2 : 1) // '1线下 2线上',
            ->where('create_at', '<', $end)
            ->count();
        // echo \think\facade\Db::table('goods')->getlastsql();exit;

        // 店铺浏览量
        // use index idx_Api_CreateAt
        $data['view_count'] = $orderType == 2?\think\facade\Db::table('member_oplog')
            ->where('api', '/api/goods/goodsList')
            ->whereBetweenTime('create_at', $date, $end)
            ->count() : 0;

        // 商品浏览数
        $data['goods_view_count'] = \think\facade\Db::table('member_oplog')
            ->where('api', '/api/goods/goodsDetail')
            ->where('tag', $orderType == 2 ? 'online' : 'offline')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

        $data['add_cart'] = $orderType == 2?\think\facade\Db::table('member_cart')
            ->where('deleted', 0)
            ->whereBetweenTime('create_at', $date, $end)
            ->sum('quantity') : 0;

        //status 0待支付 1待发货 2待收货 3已收货 4已完成 5退款中 6退款成功 7取消 8超时取消 9待退货
        $field2 = [
            'COALESCE(COUNT(DISTINCT CASE WHEN deleted = 0 THEN user_id END), 0) AS order_member', // 下单人数

            'COALESCE(SUM(CASE WHEN deleted = 0 THEN 1 ELSE 0 END), 0) AS order_count', // 订单数
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN sell_price ELSE 0 END), 0) AS order_price', // 下单金额
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN 1 ELSE 0 END), 0) AS pay_order', // 实际支付订单数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN pay_price ELSE 0 END), 0) AS pay_price', // 实际支付金额
            'COALESCE(COUNT(DISTINCT CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN user_id END), 0) AS pay_member', // 实际支付人数
        ];
        $data2 = \think\facade\Db::table('order')
            ->field($field2)
        // 1线下直付单 2线上单 3线下单 4供应链
            ->whereIn('order_type', $orderType == 2 ? [2] : [1, 3])
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        return array_merge($data, $data2);
    }

    /**
     * 统计 怡亚通
     * @param  [type] $date [description]
     * @return [type]       [description]
     */
    private static function statsSupplyOrder($date, $orderType = 4)
    {
        $end = $date . ' 23:59:59';

        // use index idx_create_at_status_deleted
        // '0待支付 1待发货 2待收款 3已收货 4已完成 5退款中 6团款成功
        $field = [
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN quantity ELSE 0 END), 0) AS order_goods', //商品下单数/下单件数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6) THEN quantity ELSE 0 END), 0) AS pay_goods', // 实际支付商品数
        ];
        $data = \think\facade\Db::table('order_goods')
            ->field($field)
            ->whereBetweenTime('create_at', $date, $end)
        // 1线下直付单 2线上单 3线下单 4供应链
            ->where('order_type', $orderType)
            ->find();
        // echo \think\facade\Db::table('order_goods')->getlastsql();
        // var_dump($data);exit;
        $data['date']       = $date;
        $data['order_type'] = $orderType;

        // 在售产品数
        // use index idx_Status_Deleted_Type_Category_Sales
        $data['sale_goods'] = \think\facade\Db::table('supply_goods')
            ->where('deleted', 0)
            ->where('status', 0) // 商品状态：0、已上架1、已下架
            ->where('create_at', '<', $end)
            ->count();
        // echo \think\facade\Db::table('goods')->getlastsql();exit;

        // 店铺浏览量
        // use index idx_Api_CreateAt
        $data['view_count'] = $orderType == 2?\think\facade\Db::table('member_oplog')
            ->where('api', '/api/supply/goodsList')
            ->whereBetweenTime('create_at', $date, $end)
            ->count() : 0;

        // 商品浏览数
        $data['goods_view_count'] = \think\facade\Db::table('member_oplog')
            ->where('api', '/api/supply/goodsDetail')
            ->whereBetweenTime('create_at', $date, $end)
            ->count();
        // echo \think\facade\Db::table('member_oplog')->getlastsql();exit;

        $data['add_cart'] = 0;

        //status 0待支付 1待发货 2待收货 3已收货 4已完成 5退款中 6退款成功 7取消 8超时取消 9待退货
        $field2 = [
            'COALESCE(COUNT(DISTINCT CASE WHEN deleted = 0 THEN user_id END), 0) AS order_member', // 下单人数

            'COALESCE(SUM(CASE WHEN deleted = 0 THEN 1 ELSE 0 END), 0) AS order_count', // 订单数
            'COALESCE(SUM(CASE WHEN deleted = 0 THEN sell_price ELSE 0 END), 0) AS order_price', // 下单金额
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN 1 ELSE 0 END), 0) AS pay_order', // 实际支付订单数
            'COALESCE(SUM(CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN pay_price ELSE 0 END), 0) AS pay_price', // 实际支付金额
            'COALESCE(COUNT(DISTINCT CASE WHEN deleted = 0 AND status IN (1, 2, 3, 4, 5, 6, 9) THEN user_id END), 0) AS pay_member', // 实际支付人数
        ];
        $data2 = \think\facade\Db::table('order')
            ->field($field2)
        // 1线下直付单 2线上单 3线下单 4供应链
            ->where('order_type', 4)
            ->whereBetweenTime('create_at', $date, $end)
            ->find();
        return array_merge($data, $data2);
    }
}
